import copy
from datetime import timedelta

from app.utils.time import ts_to_local, get_now_date
from app.utils.uuid import generate_random_chars
from app.database.postgre import SessionDep, select, col, func, and_, or_, case
from app.database.models import Account, App, CardType, Card, Device, CardStatus
from app.models import responses
from app.models.common import CardIdParams, CardIdJSON, AppIdJSON, AppIdParams
from app.models.cards import (AppCardListParams, CreateCardJSON, UpdateCardJSON, FreezeCardJSON,
                              BatchDeleteAppCardsJSON, IncreaseCardsHourJSON)


async def get_app_cards_list(
        account: Account,
        query_params: AppCardListParams,
        session: SessionDep):
    """获取应用下的卡密列表（可筛选）"""
    # 构建查询参数
    app_id = query_params.app_id
    page = query_params.page
    page_size = query_params.page_size
    card = query_params.card
    status = query_params.status
    card_type = query_params.card_type
    remark = query_params.remark
    device_code = query_params.device_code
    create_times = query_params.create_times
    activate_times = query_params.activate_times
    expire_times = query_params.expire_times

    # 获取查询的应用
    app = session.get(App, query_params.app_id)
    if not app or app.account_id != account.aid:
        return responses.Response303()

    # 创建筛选条件
    query = session.query(Card).filter(Card.app_id == app_id).order_by(col(Card.id).desc())

    if card:
        query = query.filter(col(Card.card).like(f"%{card}%"))

    status_conditions = []
    if status == CardStatus.UNKNOWN.value:  # 未激活
        status_conditions.append(and_(col(Card.is_freeze).is_(False), col(Card.activate_time).is_(None)))
    elif status == CardStatus.ACTIVE.value:  # 已激活
        status_conditions.append(
            and_(col(Card.is_freeze).is_(False),
                 col(Card.activate_time).isnot(None),
                 Card.expire_time >= get_now_date()
                 ))
    elif status == CardStatus.FREEZE.value:  # 已冻结
        status_conditions.append(Card.is_freeze)
    elif status == CardStatus.EXPIRED.value:  # 已过期
        status_conditions.append(and_(col(Card.is_freeze).is_(False), Card.expire_time < func.now()))

    if status_conditions:
        query = query.filter(or_(*status_conditions))
    if card_type:
        query = query.filter(Card.type == card_type)
    if remark:
        query = query.filter(col(Card.remark).like(f"%{remark}%"))
    if device_code:
        query = query.join(Device).filter(col(Device.code).like(f"%{device_code}%"))
    if create_times is not None:
        start = create_times.get('start')  # 秒级时间戳
        end = create_times.get('end')  # 秒级时间戳
        if start and end:
            query = query.filter(col(Card.create_time).between(ts_to_local(start), ts_to_local(end)))
    if activate_times is not None:
        start = activate_times.get('start')
        end = activate_times.get('end')
        if start and end:
            query = query.filter(col(Card.activate_time).between(ts_to_local(start), ts_to_local(end)))
    if expire_times is not None:
        start = expire_times.get('start')
        end = expire_times.get('end')
        if start and end:
            query = query.filter(col(Card.expire_time).between(ts_to_local(start), ts_to_local(end)))

    # 获取筛选后的总数
    count_query = query.with_entities(func.count()).order_by(None)
    total_count = session.execute(count_query).scalar_one()
    total_pages = (total_count + page_size - 1) // page_size

    # 对筛选后的数据进行分页
    offset = (page - 1) * page_size
    paginated_query = query.distinct(Card.id).offset(offset).limit(page_size)
    paginated_cards = paginated_query.all()

    results = []
    for card in paginated_cards:
        result = card.model_dump()
        result.update({'status': card.status})
        results.append(result)

    return responses.SuccessResponse(
        data={
            'total': total_count,
            'total_pages': total_pages,
            'page': page,
            'page_size': len(results),
            'list': results
        }
    )


async def create_cards(account: Account, json_data: CreateCardJSON, session: SessionDep):
    """批量增加卡密"""
    # 查询应用是否存在
    app = session.get(App, json_data.app_id)
    if not app or app.account_id != account.aid:
        return responses.Response303()

    # 检查应用是否被超管禁用
    if app.force_disabled:
        return responses.Response304()

    # 查询卡种是否存在
    card_type_id = json_data.card_type_id
    card_type = session.exec(
        select(CardType).where(CardType.id == card_type_id, CardType.app_id == app.id)).first()
    if not card_type:
        return responses.Response403()

    # 构建生成卡密
    card_count = json_data.card_count
    card_length = app.card_length
    card_rule = app.card_rule
    card_prefix = app.card_prefix
    card_codes = generate_random_chars(card_count, card_length, card_rule, card_prefix, app.id)

    # 批量创建卡密
    new_cards = []
    common_card_data = json_data.model_dump(exclude={'card_type_id', 'card_count'})
    for card_code in card_codes:
        card_data = copy.copy(common_card_data)
        card_data['card'] = card_code
        card_data['type'] = card_type.name
        card_data['hours'] = card_type.hours
        new_card = Card(**card_data)
        new_cards.append(new_card)
        session.add(new_card)

    # 提交所有的卡密
    session.commit()

    # 格式化结果
    results = []
    for new_card in new_cards:
        session.refresh(new_card)
        result = new_card.model_dump()
        result.update({'status': new_card.status})
        results.append(result)

    return responses.SuccessResponse(msg='卡密创建成功', data={
        'cards': results
    })


async def delete_card_by_id(account: Account, json_data: CardIdJSON, session: SessionDep):
    """删除单条卡密"""
    # 获取对应的应用
    app = session.get(App, json_data.app_id)
    if not app or app.account_id != account.aid:
        return responses.Response303()

    # 检查应用是否被超管禁用
    if app.force_disabled:
        return responses.Response304()

    # 获取被删除的卡密
    card_id = json_data.card_id
    card = session.exec(
        select(Card).where(Card.id == card_id, Card.app_id == app.id)).first()
    if not card:
        return responses.Response503()

    session.delete(card)
    session.commit()

    return responses.SuccessResponse(msg='卡密已删除', data=card.model_dump(include={'id'}))


async def delete_card_by_ids(account: Account, json_data: BatchDeleteAppCardsJSON, session: SessionDep):
    """批量删除软件卡密"""
    # 获取对应的应用
    app_id = json_data.app_id
    app = session.get(App, app_id)
    if not app or app.account_id != account.aid:
        return responses.Response303()

    # 检查应用是否被超管禁用
    if app.force_disabled:
        return responses.Response304()

    cards = session.query(Card).filter(
        Card.app_id == app_id, col(Card.id).in_(json_data.card_ids)
    ).all()
    if not cards:
        return responses.SuccessResponse(msg='没有找到指定的卡密', data={'count': 0, 'ids': []})

    deleted_ids = [card.id for card in cards]

    session.query(Card).filter(
        Card.app_id == app_id, col(Card.id).in_(deleted_ids)
    ).delete(synchronize_session=False)

    session.commit()

    return responses.SuccessResponse(msg='选中的卡密已删除', data={
        'count': len(deleted_ids),
        'ids': deleted_ids
    })


async def clear_all_app_cards(account: Account, json_data: AppIdJSON, session: SessionDep):
    """清空所有的卡密"""
    # 获取对应的应用
    app_id = json_data.app_id
    app = session.get(App, app_id)
    if not app or app.account_id != account.aid:
        return responses.Response303()

    # 检查应用是否被超管禁用
    if app.force_disabled:
        return responses.Response304()

    # 获取该应用的所有卡密
    cards = session.query(Card).filter(Card.app_id == app_id).all()
    if not cards:
        return responses.SuccessResponse(msg='该应用没有卡密', data={'ids': []})

    deleted_ids = [card.id for card in cards]

    session.query(Card).filter(Card.app_id == app_id).delete(synchronize_session=False)
    session.commit()

    return responses.SuccessResponse(msg='卡密已清空', data={
        'ids': deleted_ids
    })


async def cleanup_expired_cards(account: Account, json_data: AppIdJSON, session: SessionDep):
    """清空所有已过期的卡密"""
    # 获取对应的应用
    app = session.get(App, json_data.app_id)
    if not app or app.account_id != account.aid:
        return responses.Response303()

    # 检查应用是否被超管禁用
    if app.force_disabled:
        return responses.Response304()

    # 获取过期卡密
    now = get_now_date()
    expired_cards = session.query(Card).filter(Card.app_id == app.id, Card.expire_time < now).all()

    # 批量删除
    if expired_cards:
        session.query(Card) \
            .filter(col(Card.id).in_([c.id for c in expired_cards])) \
            .delete(synchronize_session=False)

        # 记录删除的ID
        deleted_ids = [c.id for c in expired_cards]
    else:
        deleted_ids = []

    session.commit()
    return responses.SuccessResponse(
        msg='过期卡密清理成功', data={
            'card_ids': deleted_ids,
            'count': len(deleted_ids)
        })


async def get_card_info(account: Account, query_params: CardIdParams, session: SessionDep):
    """获取单条卡密信息"""
    # 获取对应的应用
    app = session.get(App, query_params.app_id)
    if not app or app.account_id != account.aid:
        return responses.Response303()

    # 获取查询的卡密
    card_id = query_params.card_id
    card = session.exec(
        select(Card).where(Card.id == card_id, Card.app_id == app.id)).first()
    if not card:
        return responses.Response503()

    data = card.model_dump()
    data.update({'status': card.status})

    return responses.SuccessResponse(data=data)


async def update_card_by_id(account: Account, json_data: UpdateCardJSON, session: SessionDep):
    """更新单条卡密"""
    # 获取对应的应用
    app = session.get(App, json_data.app_id)
    if not app or app.account_id != account.aid:
        return responses.Response303()

    # 检查应用是否被超管禁用
    if app.force_disabled:
        return responses.Response304()

    # 构建更新卡密的参数
    card_id = json_data.card_id
    other_data = json_data.model_dump(exclude={'app_id', 'card_id'}, exclude_none=True)

    # 获取对应的卡密
    card = session.exec(
        select(Card).where(Card.id == card_id, Card.app_id == app.id)).first()
    if not card:
        return responses.Response503()

    Card.sqlmodel_update(card, other_data)
    session.commit()
    session.refresh(card)

    data = card.model_dump()
    data.update({'status': card.status})

    return responses.SuccessResponse(msg='卡密更新成功', data=data)


async def freeze_card_by_id(account: Account, json_data: FreezeCardJSON, session: SessionDep):
    """冻结单条卡密"""
    # 获取对应的应用
    app = session.get(App, json_data.app_id)
    if not app or app.account_id != account.aid:
        return responses.Response303()

    # 检查应用是否被超管禁用
    if app.force_disabled:
        return responses.Response304()

    # 构建更新卡种的参数
    card_id = json_data.card_id
    is_freeze = json_data.is_freeze

    # 获取对应的卡密
    card = session.exec(
        select(Card).where(Card.id == card_id, Card.app_id == app.id)).first()
    if not card:
        return responses.Response503()

    card.is_freeze = is_freeze
    session.commit()
    session.refresh(card)

    return responses.SuccessResponse(
        msg='卡密冻结成功' if card.is_freeze else '卡密解冻成功',
        data=card.model_dump(include={'id', 'is_freeze'}))


async def get_card_statistics(account: Account, query_params: AppIdParams, session: SessionDep):
    """获取卡密统计"""
    # 获取对应的应用
    app = session.get(App, query_params.app_id)
    if not app or app.account_id != account.aid:
        return responses.Response303()

    # 构建统计查询条件
    current_time = get_now_date()

    # 使用 SQL CASE 表达式进行多条件统计
    stats_query = session.query(
        func.count(Card.id).label("total"),  # 卡密总数
        func.sum(
            case((Card.is_freeze == True, 1), else_=0)
        ).label("freeze_count"),  # 已冻结数量
        func.sum(
            case((
                and_(
                    col(Card.activate_time).isnot(None),
                    Card.expire_time < current_time
                ), 1
            ), else_=0)
        ).label("expired_count"),  # 已过期数量
        func.sum(
            case((
                and_(
                    col(Card.activate_time).isnot(None),
                    Card.expire_time >= current_time,
                    Card.is_freeze == False
                ), 1
            ), else_=0)
        ).label("active_count"),  # 已激活且有效数量
        func.sum(
            case((
                and_(
                    col(Card.activate_time).is_(None),
                ), 1
            ), else_=0)
        ).label("unused_count")  # 未使用数量
    ).filter(Card.app_id == app.id)

    # 执行查询
    result = stats_query.one()

    return responses.SuccessResponse(data={
        "total": result.total or 0,
        "freeze_count": result.freeze_count or 0,
        "expired_count": result.expired_count or 0,
        "active_count": result.active_count or 0,
        "unused_count": result.unused_count or 0
    })


async def get_card_devices(account: Account, query_params: CardIdParams, session: SessionDep):
    """获取卡密已绑定的设备"""
    app_id = query_params.app_id
    card_id = query_params.card_id

    app = session.get(App, app_id)
    if not app or app.account_id != account.aid:
        return responses.Response303()

    query = (
        select(Device)
        .join(Card, Card.id == Device.card_id)
        .where(
            Card.id == card_id,
            Card.app_id == app_id,
            Device.app_id == app_id
        )
    )

    devices = session.exec(query).all()
    if not devices:
        responses.SuccessResponse(data={
            'card_id': card_id,
            'devices': []
        })

    results = []
    for device in devices:
        result = device.model_dump()
        result['status'] = device.status
        results.append(result)

    return responses.SuccessResponse(data={
        'card_id': card_id,
        'devices': results
    })


async def increase_cards_hour(account: Account, json_data: IncreaseCardsHourJSON, session: SessionDep):
    """增加卡密时长"""
    app_id = json_data.app_id
    card_ids = json_data.card_ids
    increase_hour = json_data.increase_hour

    # 获取应用
    app = session.get(App, app_id)
    if not app or app.account_id != account.aid:
        return responses.Response303()

    # 检查应用是否被超管禁用
    if app.force_disabled:
        return responses.Response304()

    # 批量查询卡密
    cards = session.exec(
        select(Card).where(
            col(Card.id).in_(card_ids),
            Card.app_id == app_id
        )
    ).all()
    if not cards:
        return responses.SuccessResponse(data={'ids': [], 'count': 0})

    success = []
    failure = []
    for card in cards:
        if not card.activate_time or not card.expire_time:
            failure.append(card)
            continue

        card.expire_time += timedelta(hours=increase_hour)
        session.add(card)
        success.append(card)

    session.commit()

    return responses.SuccessResponse(data={
        'success_count': len(success),
        'success_ids': [c.id for c in success],
        'failure_count': len(failure),
        'failure_ids': [c.id for c in failure]
    })
